﻿Imports System.Data.SqlClient

Module Production_Utilities


    Public Function GetAbbreviation(ByVal InsureClass As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(Abbreviation,N'') From InsurClasses Where ClassName=@ClassName", cnn4)
            Dim Abbreviation As String

            cnn4.Open()

            'Add values
            cmd.Parameters.AddWithValue("@ClassName", InsureClass)

            Abbreviation = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return Abbreviation
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetRetailClientAccNo(ByVal ClientName As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNo,N'') From RetailClients Where FullName=@ClientName", cnn4)
            Dim ClientAccNo As String

            cnn4.Open()
            cmd.Parameters.AddWithValue("@ClientName", ClientName)
            ClientAccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return ClientAccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetClientAccNo(ByVal ClientID As Integer) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNo,N'') From Clients Where SNo=@ClientID", cnn4)
            Dim ClientAccNo As String

            cnn4.Open()
            cmd.Parameters.AddWithValue("@ClientID", ClientID)
            ClientAccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return ClientAccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetInsurClassAccNo(ByVal ClassName As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNo,N'') From InsurClasses Where ClassName=@ClassName", cnn4)

            Dim InsurClassAccNo As String

            cnn4.Open()
            cmd.Parameters.AddWithValue("@ClassName", ClassName)
            InsurClassAccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return InsurClassAccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetRetailClientName(ByVal ClientCode As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(FullName,N'') From RetailClients Where Code=N'" & ClientCode & "'", cnn4)
            Dim ClientName As String

            cnn4.Open()
            ClientName = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return ClientName
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetInsurCompPremAccNo(ByVal CompanyName As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNoPrem,N'') From InsuranceCompanies Where CompanyName=@CompanyName", cnn4)
            Dim CompanyAccNo As String

            cnn4.Open()
            cmd.Parameters.AddWithValue("@CompanyName", CompanyName)
            CompanyAccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return CompanyAccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetInsurCompAccruedCommAccNo(ByVal CompanyName As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNoComm,N'') From InsuranceCompanies Where CompanyName=@CompanyName", cnn4)
            Dim CompanyAccNo As String

            cnn4.Open()
            cmd.Parameters.AddWithValue("@CompanyName", CompanyName)
            CompanyAccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return CompanyAccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetProducerAccNo(ByVal ProducerName As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNo,N'') From Producers Where FullName=@ProducerName", cnn4)
            Dim ProducerAccNo As String

            cnn4.Open()
            cmd.Parameters.AddWithValue("@ProducerName", ProducerName)
            ProducerAccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return ProducerAccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetProducerTarget(ByVal SalesmanUserName As String) As Double
        Try
            Dim cmd As New SqlCommand("Select IsNull(dbo.GetPremTargetStaff(Year(GetDate()),Month(GetDate()),N'" & SalesmanUserName & "'),0)", cnn4)
            Dim Target As Double

            cnn4.Open()
            Target = CDbl(cmd.ExecuteScalar)
            cnn4.Close()

            Return Target
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetProducerAchieved(ByVal SalesmanUserName As String) As Double
        Try
            Dim cmd As New SqlCommand("Select IsNull(dbo.GetPremActualStaff(Year(GetDate()),Month(GetDate()),N'" & SalesmanUserName & "'),0)", cnn4)
            Dim Target As Double

            cnn4.Open()
            Target = CDbl(cmd.ExecuteScalar)
            cnn4.Close()

            Return Target
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetInsurClassList() As ArrayList
        Try
            Dim cmd As New SqlCommand("Select Distinct ClassName From InsurClasses", cnn)
            Dim Reader As SqlDataReader

            Dim InsurClasses As New ArrayList

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                InsurClasses.Add(Reader.Item(0))
            End While
            cnn.Close()

            Return InsurClasses
        Catch ex As Exception
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetLinesofBusinessList(ByVal InsurComp As String) As ArrayList
        Try
            Dim cmd As New SqlCommand("Select Distinct LineofBusiness From InsurLinesofBusiness Where ClassName=@ClassName", cnn4)
            Dim Reader As SqlDataReader

            Dim LinesofBusiness As New ArrayList

            cmd.Parameters.AddWithValue("@ClassName", InsurComp)

            cnn4.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                LinesofBusiness.Add(Reader.Item(0))
            End While
            cnn4.Close()

            Return LinesofBusiness
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetInsurCompaniesList() As ArrayList
        Try
            Dim cmd As New SqlCommand("Select CompanyName from InsuranceCompanies", cnn)
            Dim Reader As SqlDataReader

            Dim InsurCompanies As New ArrayList

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                InsurCompanies.Add(Reader.Item(0))
            End While
            cnn.Close()

            Return InsurCompanies
        Catch ex As Exception
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetRetailClientsList() As ArrayList
        Try
            Dim cmd As New SqlCommand("Select FullName from RetailClients", cnn)
            Dim Reader As SqlDataReader

            Dim RetailList As New ArrayList

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                RetailList.Add(Reader.Item(0))
            End While
            cnn.Close()

            Return RetailList
        Catch ex As Exception
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetProducersList() As ArrayList
        Try
            Dim cmd As New SqlCommand("Select FullName from Producers", cnn)
            Dim Reader As SqlDataReader

            Dim ProducersList As New ArrayList

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                ProducersList.Add(Reader.Item(0))
            End While
            cnn.Close()

            Return ProducersList
        Catch ex As Exception
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetClientInsurCompBalance(ByVal ClientID As Integer, ByVal InsurComp As String) As Double
        Try
            Dim cmd As New SqlCommand("Select IsNull(Sum(TotalOut)-Sum(TotalIn),0) " & _
                                      "From ClientsAcc Where ClientID=" & ClientID & " and InsurCompany=N'" & InsurComp & "'", cnn4)
            Dim Balance As Double

            cnn4.Open()
            Balance = CDbl(cmd.ExecuteScalar)
            cnn4.Close()

            Return Balance
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetInsurCompPolAccNo(ByVal InsurComp As String, ByVal PolNo As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNo,N'-') From Policies Where InsurComp=N'" & InsurComp & _
                                      "' and PolicyNo=N'" & PolNo & "'", cnn)
            Dim AccNo As String

            cnn.Open()
            AccNo = cmd.ExecuteScalar
            cnn.Close()

            Return AccNo
        Catch ex As Exception
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function


End Module
